package kr.hkit.android_bookmgr.DAO;


import java.util.ArrayList;

import kr.hkit.android_bookmgr.DTO.BookData;
import kr.hkit.android_bookmgr.DTO.FinishedBook;
import kr.hkit.android_bookmgr.DTO.MemoData;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class BookDAO extends SQLiteOpenHelper { //pdf cp부분 20page - 사용!!

	static final String DATABASE_NAME1 = "Book.db"; //애뮬
	static final String DATABASE_NAME2 = "/storage/emulated/0/Book.db"; //폰
	static final String TABLE_BOOK = "booklist";
	static final String TABLE_MEMO = "memolist";
	static final int DATABASE_VER = 2;
	
	Context mContext;
	private static BookDAO mBookMgr;
	
	
	public BookDAO(Context context) {
		//super(context, DATABASE_NAME1, null, DATABASE_VER); //애뮬
		super(context, DATABASE_NAME2, null, DATABASE_VER); //폰 
		mContext = context;
	}

	public static BookDAO getInstance(Context context) {
		if (mBookMgr==null){
			mBookMgr = new BookDAO(context);
		}
		return mBookMgr;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//booklist Table
		db.execSQL("create table if not exists booklist ( _id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, author TEXT, publisher TEXT, totalpage INTEGER, currentpage INTEGER, status INTEGER, bookimg TEXT, imgtype INTEGER, startdate TEXT, enddate TEXT);");
		//memolist Table
				db.execSQL("create table if not exists memolist ( _id INTEGER PRIMARY KEY AUTOINCREMENT, writedate TEXT, review TEXT, words TEXT, wordspage TEXT, memoimg TEXT, memoimgtype INTEGER, bookid INTEGER);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists booklist");
		db.execSQL("drop table if exists memolist");
		onCreate(db);
	}
	
	//BOOK!!!
	public long insertBook(ContentValues addRowValue) {
		return getWritableDatabase().insert(TABLE_BOOK, null, addRowValue);
	}

	public int updateBook(ContentValues updateRowValues, String whereClause, String[] whereArgs){
		return getWritableDatabase().update(TABLE_BOOK, updateRowValues, whereClause, whereArgs);
	}
	
	public int deleteBook(String whereClause, String[] whereArgs){
		return getWritableDatabase().delete(TABLE_BOOK, whereClause, whereArgs);
	}
	

	
	public Cursor selectGridItem(int id) { //책갈피용
		
		SQLiteDatabase db;
		db = this.getReadableDatabase();
		
		String strSQL = "select * from booklist where _id='"+id+"'";
		Cursor cursor = db.rawQuery(strSQL, null);
		
		return cursor;
	}
	
	
	//다시시도 해보쟈 
	
	public ArrayList<BookData> selectListMain(){ //메인
		
		SQLiteDatabase db;
		db = this.getReadableDatabase();
		
		String strSQL = "select _id, bookimg, imgtype, name, (currentpage*100/totalpage*100)/100 'progress' from booklist order by _id desc";
		Cursor c = db.rawQuery(strSQL, null);
		//startManagingCursor(c);
		ArrayList<BookData> arItems = new ArrayList<BookData>();
		
		while (c.moveToNext()) { //리스트표기용
			int mId = c.getInt(c.getColumnIndex("_id"));
			String mBookimg = c.getString(c.getColumnIndex("bookimg"));
			String mName = c.getString(c.getColumnIndex("name"));
			int mImgtype = c.getInt(c.getColumnIndex("imgtype"));
			int mProgress = c.getInt(c.getColumnIndex("progress"));
			
			arItems.add(new BookData(mId, mBookimg, mName, mImgtype, mProgress));
		}
		
		return arItems;
	}
	
	
	public ArrayList<BookData> selectListMainSpinner(int status){ //메인 - 스피너 필터용
		
		SQLiteDatabase db;
		db = this.getReadableDatabase();
		
		String strSQL = "select _id, bookimg, imgtype, name, (currentpage*100/totalpage*100)/100 'progress' from booklist where status='"+status+"' order by _id desc";
		Cursor c = db.rawQuery(strSQL, null);
		//startManagingCursor(c);
		ArrayList<BookData> arItems = new ArrayList<BookData>();
		
		while (c.moveToNext()) { //리스트표기용
			int mId = c.getInt(c.getColumnIndex("_id"));
			String mBookimg = c.getString(c.getColumnIndex("bookimg"));
			String mName = c.getString(c.getColumnIndex("name"));
			int mImgtype = c.getInt(c.getColumnIndex("imgtype"));
			int mProgress = c.getInt(c.getColumnIndex("progress"));
			
			arItems.add(new BookData(mId, mBookimg, mName, mImgtype, mProgress));
		}
		
		return arItems;
	}
	
	
	//메인 있는 것 중 이름으로 검색필터용
	public ArrayList<BookData> selectListSearchByName(String qry){ 
		
		SQLiteDatabase db;
		db = this.getReadableDatabase();
		
		String strSQL = "select _id, bookimg, imgtype, name, (currentpage*100/totalpage*100)/100 'progress' from booklist where name like '"+qry+"' order by _id desc"; 
		Cursor c = db.rawQuery(strSQL, null);
		ArrayList<BookData> arItems = new ArrayList<BookData>();
		
		while (c.moveToNext()) { //리스트표기용
			int mId = c.getInt(c.getColumnIndex("_id"));
			String mBookimg = c.getString(c.getColumnIndex("bookimg"));
			String mName = c.getString(c.getColumnIndex("name"));
			int mImgtype = c.getInt(c.getColumnIndex("imgtype"));
			int mProgress = c.getInt(c.getColumnIndex("progress"));
			
			Log.d("qry", mName);
			
			arItems.add(new BookData(mId, mBookimg, mName, mImgtype, mProgress));
		}
		
		return arItems;
	}
	
		
		//완독표시용!
		public ArrayList<FinishedBook> selectStatistic() {
			
			SQLiteDatabase db;
			db = this.getReadableDatabase();

			String strSQL = "select substr(enddate,6,2) 'month', count(*) 'count' from booklist where status=3 group by substr(enddate,6,2)";
			Cursor c = db.rawQuery(strSQL, null);
			ArrayList<FinishedBook> finishedBooks = new ArrayList<FinishedBook>();

			while (c.moveToNext()) { // 리스트표기용
				int mMonth = c.getInt(c.getColumnIndex("month"));
				int mCount = c.getInt(c.getColumnIndex("count"));
			
				finishedBooks.add(new FinishedBook(mMonth, mCount));
			}
			
			return finishedBooks;
		}
		
		
		
		//여기부터 메모테이블 
		//MEMO!!!
		public long insertMemo(ContentValues addRowValue) {
			return getWritableDatabase().insert(TABLE_MEMO, null, addRowValue);
		}
		
		//MEMO!!
		public int updateMemo(ContentValues updateRowValues, String whereClause, String[] whereArgs){
			return getWritableDatabase().update(TABLE_MEMO, updateRowValues, whereClause, whereArgs);
		}
		
		//MEMO!!
		public int deleteMemo(String whereClause, String[] whereArgs){
			return getWritableDatabase().delete(TABLE_MEMO, whereClause, whereArgs);
		}
		
			
			//MEMO 리스팅 - 이걸로!
		public ArrayList<MemoData> selectListMemoByBookId(int bookid) { //실험중
				
				SQLiteDatabase db;
				db = this.getReadableDatabase();

				String strSQL = "select * from memolist where bookid='"+bookid+"' order by _id desc";
				Cursor c = db.rawQuery(strSQL, null);
				ArrayList<MemoData> memoItems = new ArrayList<MemoData>();

				while (c.moveToNext()) { // 리스트표기용
					int mMemoId = c.getInt(c.getColumnIndex("_id"));
					String mWritedate = c.getString(c.getColumnIndex("writedate"));
					String mReview = c.getString(c.getColumnIndex("review"));
					String mWords = c.getString(c.getColumnIndex("words"));
					int mWordspage = c.getInt(c.getColumnIndex("wordspage"));
					String mMemoimg = c.getString(c.getColumnIndex("memoimg"));
					int mMemoimgtype = c.getInt(c.getColumnIndex("memoimgtype"));
					int mBookId = c.getInt(c.getColumnIndex("bookid"));

					memoItems.add(new MemoData(mMemoId, mWritedate, mReview, mWords, mWordspage, mMemoimg, mMemoimgtype, mBookId ));
				}

				return memoItems;
			}
		
			
			//MEMO 수정용
			public ArrayList<MemoData> selectEditMemo(int id) {
				
				SQLiteDatabase db;
				db = this.getReadableDatabase();

				String strSQL = "select * from memolist where _id='"+id+"'";
				Cursor c = db.rawQuery(strSQL, null);
				ArrayList<MemoData> memoItems = new ArrayList<MemoData>();

				while (c.moveToNext()) { // 
					int mMemoId = c.getInt(c.getColumnIndex("_id"));
					String mWritedate = c.getString(c.getColumnIndex("writedate"));
					String mReview = c.getString(c.getColumnIndex("review"));
					String mWords = c.getString(c.getColumnIndex("words"));
					int mWordspage = c.getInt(c.getColumnIndex("wordspage"));
					String mMemoimg = c.getString(c.getColumnIndex("memoimg"));
					int mMemoimgtype = c.getInt(c.getColumnIndex("memoimgtype"));

					memoItems.add(new MemoData(mMemoId, mWritedate, mReview, mWords, mWordspage, mMemoimg, mMemoimgtype));
				}

				return memoItems;
			}
			
	
}
